Food Inflation and Wage Growth in Indonesia

Analyze and visualize the food inflation and wage growth in Indonesia from 2010 to 2021.

1. Install and load the necessary library.

library(RMySQL)

Loading required package: DBI

library(tidyverse)

── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──

✔ ggplot2 3.3.6     ✔ purrr   0.3.4
✔ tibble  3.1.7     ✔ dplyr   1.0.9
✔ tidyr   1.2.0     ✔ stringr 1.4.0
✔ readr   2.1.2     ✔ forcats 0.5.1

── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

library(DT)
library(gganimate)
library(ggimage)

RMySQL package is needed to connect to MySQL database. Tidyverse package is used for data processing and visualization. DT improves the quality of the table display on the HTML page.

2. Connect to local MySQL server.

mydb <- dbConnect(RMySQL::MySQL(),
                 dbname='commodity_price',
                 host='localhost',
                 port=3306,
                 user = user,
                 password = password)

3. Show the tables inside the database.

dbListTables(mydb)

[1] "food_commodity_price" "minimum_wage"        

food_commodity_price

minimum_wage

The raw data for food commodity price can be downloaded on https://aplikasi2.pertanian.go.id/simharga2017/konsumen_kota/hkp4. The raw data for minimum wage (except for 2017 and 2021) can be downloaded on https://www.bps.go.id/indicator/19/220/1/upah-minimum-regional-propinsi.html. The average minimum wage for 2017 and 2021 is calculated from https://finance.detik.com/berita-ekonomi-bisnis/d-3356762/ini-dia-daftar-lengkap-ump-2017-di-34-provinsi and https://finance.detik.com/berita-ekonomi-bisnis/d-5326918/lengkap-rincian-ump-2021-di-34-provinsi.

4. Write a query to select all rows and columns from the food_commodity_price and minimum_wage table, then show the results.

result_1 <- dbGetQuery(mydb, "SELECT * FROM food_commodity_price")

datatable(
  result_1,
  extensions = "FixedColumns",
  options = list(
    paging = TRUE, searching = TRUE,
    scrollX = TRUE, fixedColumns = list(leftColumns = 2)
  )
)

result_2 <- dbGetQuery(mydb, "SELECT * FROM minimum_wage")

datatable(
  result_2,
  extensions = "FixedColumns",
  options = list(
    paging = TRUE, searching = TRUE,
    scrollX = TRUE, fixedColumns = list(leftColumns = 2)
  )
)

5. The data for food commodity price are written in wide format, so let’s convert the data into long format, then save the data as a view.

dbGetQuery(mydb, "DROP VIEW IF EXISTS fcp_long")

dbGetQuery(mydb, "CREATE VIEW fcp_long AS
                           SELECT `Komoditas Pangan` AS food_commodity, '2010' AS year, `2010` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2011' AS year, `2011` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2012' AS year, `2012` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2013' AS year, `2013` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2014' AS year, `2014` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2015' AS year, `2015` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2016' AS year, `2016` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2017' AS year, `2017` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2018' AS year, `2018` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2019' AS year, `2019` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2020' AS year, `2020` AS price_per_kg
                           FROM food_commodity_price UNION ALL
                           SELECT `Komoditas Pangan` AS food_commodity, '2021' AS year, `2021` AS price_per_kg
                           FROM food_commodity_price
                           ORDER BY food_commodity, year")

6. Show the results of the view.

result_3 <- dbGetQuery(mydb, "SELECT * FROM fcp_long")

datatable(
  result_3,
  extensions = 'Scroller',
  options = list(
    deferRender = TRUE,
    scrollY = 200,
    scroller = TRUE
  )
)

7. For visualization purpose, the food commodity with any null value in the price column will be excluded.

result_4 <- dbGetQuery(mydb, "SELECT food_commodity FROM fcp_long GROUP BY 1 HAVING MIN(price_per_kg) <> 0")
result_4

The following food commodities have no null value in the price column: shallot (bawang merah), rice (beras), red chili (cabe merah), curly red chili (cabe merah keriting), chicken meat (daging ayam broiler), beef (daging sapi), white sugar (gula pasir lokal), cooking oil (minyak goreng kemasan), and chicken egg (telur ayam ras). I am going to exclude curly red chili because it’s very similar to red chili.

8. Combine the minimum wage data with the food price using UNION. Then calculate the YoY and cumulative growth percentage for food’s price and minimum wage.

food_wage_df <- dbGetQuery(mydb, "WITH food_wage AS (SELECT *
                  FROM fcp_long
                  WHERE food_commodity <> 'Cabe Merah Keriting'
                    AND food_commodity IN (SELECT food_commodity
                                           FROM fcp_long
                                           GROUP BY 1
                                           HAVING MIN(price_per_kg) <> 0)
                  UNION
                  SELECT nama_variabel, nama_tahun, data_content
                  FROM minimum_wage
                  ORDER BY 1, 2)
                  SELECT food_commodity AS food_or_wage, CAST(year AS SIGNED) as year,
                        price_per_kg AS price_per_kg_or_minimum_wage,
                        (price_per_kg - (LAG(price_per_kg) OVER (PARTITION BY food_commodity ORDER BY year))) /
                        (LAG(price_per_kg) OVER (PARTITION BY food_commodity ORDER BY year)) * 100 AS growth_percentage,
                        (price_per_kg - (FIRST_VALUE(price_per_kg) OVER (PARTITION BY food_commodity ORDER BY year))) /
                        (FIRST_VALUE(price_per_kg) OVER (PARTITION BY food_commodity ORDER BY year)) * 100 AS cumulative_gp
                  FROM food_wage")

Warning in .local(conn, statement, ...): Decimal MySQL column 3 imported as
numeric

Warning in .local(conn, statement, ...): Decimal MySQL column 4 imported as
numeric

datatable(
  food_wage_df,
  extensions = 'Scroller',
  options = list(
    deferRender = TRUE,
    scrollY = 200,
    scroller = TRUE
  )
)

CAST function is used to change the year to signed integer. This allows R to save them as dbl (double-precision floating point number) instead of chr (characters).

9. Visualize the cumulative growth percentage for food’s price and minimum wage since 2010.

food_wage_df[is.na(food_wage_df)] <- 0
food_wage_df %>%
  ggplot(aes(cumulative_gp, food_or_wage)) +
  geom_col(aes(fill = food_or_wage)) +
  facet_wrap(vars(year))

Make a new dataframe to rank the food commodities and wage based on the cumulative percent growth for each year, and translate the items from Indonesian to English.

food_wage_df2 <- food_wage_df %>%
  group_by(year) %>%
  arrange(year, desc(cumulative_gp)) %>%
  mutate(ranking = row_number())

food_wage_df2[food_wage_df2 == "Upah Minimum Regional/Propinsi"] <- "Min Wage"
food_wage_df2[food_wage_df2 == "Daging Ayam Broiler"] <- "Chicken"
food_wage_df2[food_wage_df2 == "Cabe Merah"] <- "Red Chili"
food_wage_df2[food_wage_df2 == "Bawang Merah"] <- "Shallot"
food_wage_df2[food_wage_df2 == "Telur Ayam Ras"] <- "Egg"
food_wage_df2[food_wage_df2 == "Gula Pasir Lokal"] <- "Sugar"
food_wage_df2[food_wage_df2 == "Minyak Goreng Kemasan"] <- "Cooking Oil"
food_wage_df2[food_wage_df2 == "Beras"] <- "Rice"
food_wage_df2[food_wage_df2 == "Daging Sapi"] <- "Beef"

datatable(
  food_wage_df2,
  extensions = 'Scroller',
  options = list(
    deferRender = TRUE,
    scrollY = 200,
    scroller = TRUE
  )
)

Make a new dataframe which contain the image path for each item, which will be used for visualization. All of the images are downloaded from freepik.com under free license. Beef, egg, and chicken are designed by Terdpongvector. Rice, red chili, and shallot are designed by macrovector. Cooking oil is designed by valadzionak_volha. Sugar is desgined by pch.vector. Wage is desgined by katemangostar.

img_df <- data.frame(food_or_wage = c("Min Wage", "Chicken", "Red Chili", "Shallot", "Egg", "Sugar", "Cooking Oil", "Rice", "Beef"),
                     image = c("C:/Users/Marselo/Downloads/food images/wage.png",
                               "C:/Users/Marselo/Downloads/food images/chicken.png",
                               "C:/Users/Marselo/Downloads/food images/red_chili.png",
                               "C:/Users/Marselo/Downloads/food images/shallot.png",
                               "C:/Users/Marselo/Downloads/food images/egg.png",
                               "C:/Users/Marselo/Downloads/food images/sugar.png",
                               "C:/Users/Marselo/Downloads/food images/cooking_oil.png",
                               "C:/Users/Marselo/Downloads/food images/rice.png",
                               "C:/Users/Marselo/Downloads/food images/beef.png"),
                               stringsAsFactors = F)
img_df

static_plot <- food_wage_df2 %>%
  left_join(img_df, by = "food_or_wage") %>%
  ggplot(aes(ranking, cumulative_gp)) +
  geom_col(aes(fill = food_or_wage), alpha=0.7) +
  coord_flip(clip = "off", expand = FALSE) +
  scale_x_reverse() +
  scale_y_continuous(labels = function(x) paste0(x, '%'))+
  geom_text(aes(ranking, cumulative_gp, label = paste0(scales::number(cumulative_gp, acc = 0.1), "% ")),
            hjust = ifelse(food_wage_df2$cumulative_gp < 0, 0, 1), vjust=0.75, size=5) +
  geom_text(aes(ranking, cumulative_gp, label = paste0(food_or_wage," ")),
            hjust = ifelse(food_wage_df2$cumulative_gp < 0, 0, 1), vjust=-0.75, size=5, fontface="bold") +
  geom_image(aes(ranking, y=-40, image = image),
             hjust = 0, size=0.08,) +
  labs(title='Cumulative Food Inflation and Wage Growth in Indonesia',
       subtitle = '2010-{closest_state}', x = "",
       y = "Cumulative Percentage Change",
       caption = 'Data Source: aplikasi2.pertanian.go.id & bps.go.id. Image Source: freepik.com.') +
  theme_minimal() +
  theme(
    axis.text.y = element_blank(),
    axis.ticks.y = element_blank(),
    axis.title.x = element_text(size=12),
    plot.title = element_text(hjust = 0.5, size = 24),
    plot.subtitle = element_text(hjust = 0.5, size = 24),
    plot.caption = element_text(hjust = 0.5, color='gray', size=10),
    plot.margin = margin(1,1,1,1, "cm"),
    panel.grid.major.y = element_blank(),
    panel.grid.minor.y = element_blank(),
    panel.grid.minor.x = element_blank()
  ) +
  guides(fill = "none")

animated_plot <- static_plot + transition_states(year, transition_length = 4, state_length = 1) +
  ease_aes('cubic-in-out')

gif_animation <- animate(animated_plot,100,fps = 20,duration = 10, width = 1000, height = 1000, renderer = gifski_renderer())
gif_animation

anim_save("food_price_4.gif",animation = gif_animation)